using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Common;
using MinCultura.Sipa.DataAccess.AgentesArtes;
using SystemFrameworks;
using AgentesCaracteristicasData = MinCultura.Sipa.DataAccess.AgentesArtes.AgentesCaracteristicasData;

namespace MinCultura.Sipa.AgentesArtes
{
	/// <summary>
	/// Clase de acceso a datos para la entidad ART_AGENTES_CARACTERISTICAS
	/// </summary>
	public class AgentesCaracteristicas 
	{
		#region Private Data Access Atributes

		private const String AGE_ID_PARAM = "@AGE_ID";
		private const String AGE_DESARROLLO_PRACTICA_ARTISTICA_PARAM = "@AGE_DESARROLLO_PRACTICA_ARTISTICA";
		private const String AGE_MANERA_PRACTICA_ARTISTICA_PARAM = "@AGE_MANERA_PRACTICA_ARTISTICA";
		private const String ARD_ID_PARAM = "@ARD_ID";
		private const String AGE_OTRA_AREA_PARAM = "@AGE_OTRA_AREA";
		private const String AGE_OTRA_AREA_ARTISTICA_TEMA_PARAM = "@AGE_OTRA_AREA_ARTISTICA_TEMA";
		private const String AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_PARAM = "@AGE_HACE_PARTE_ORGANIZACION_ARTISTICA";
		private const String AGE_ORGANIZACION_ARTISTICA_ACTUAL_PARAM = "@AGE_ORGANIZACION_ARTISTICA_ACTUAL";
		private const String AGE_ANO_VINCULACION_ACTUAL_PARAM = "@AGE_ANO_VINCULACION_ACTUAL";
		private const String AGE_MODALIDAD_FORMACION_ARTISTICA_PARAM = "@AGE_MODALIDAD_FORMACION_ARTISTICA";
		private const String NIV_ID_PARAM = "@NIV_ID";
		private const String AGE_TITULO_PARAM = "@AGE_TITULO";
		private const String AGE_ANO_TITULO_PARAM = "@AGE_ANO_TITULO";
		private const String AGE_ENTIDAD_EDUCATIVA_PARAM = "@AGE_ENTIDAD_EDUCATIVA";
		public const string AGE_EDU_EN_CURSO_PARAM = "@AGE_EDU_EN_CURSO";
		public const string AGE_EDU_SEMESTRES_PARAM = "@AGE_EDU_SEMESTRES";
		public const string AGE_EDU_ENTIDAD_PARAM = "@AGE_EDU_ENTIDAD";

	
		#endregion Private Data Access Atributes

        #region propiedades

        private string _cadenaConexion;

        public string ConnectionString
        {
            get { return _cadenaConexion; }
            set { _cadenaConexion = value; }
        }

        #endregion


		/// <summary>
		/// Constructor de la clase ARTAGENTESCARACTERISTICAS
		/// </summary>
        public AgentesCaracteristicas(string strCadenaConecxion)
        {
            ConnectionString = strCadenaConecxion;
        }
		public AgentesCaracteristicasData BuscarAgentesCaracteristicas()
		{
			AgentesCaracteristicasData response = new AgentesCaracteristicasData();
			try
			{
				SqlHelper.ExecuteDataTable(ConnectionString, CommandType.StoredProcedure, "PAS_BUSCAR_ART_AGENTES_CARACTERISTICAS", response);
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_BUSCAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorBuscar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			if (response.Rows.Count == 0)
				return null;
			return response;
		}


		public AgentesCaracteristicasData ConsultarAgentesCaracteristicas(decimal parAGE_ID)
		{
			AgentesCaracteristicasData response = new AgentesCaracteristicasData();
			SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, parAGE_ID);
			SqlParameter[] parameters = {param1};
			try
			{
				SqlHelper.ExecuteDataTable(ConnectionString, CommandType.StoredProcedure, "PAS_CONSULTAR_ART_AGENTES_CARACTERISTICAS", response, parameters);
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_CONSULTAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorConsultar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			if (response.Rows.Count == 0)
				return null;
			return response;
		}

		public bool AdicionarAgentesCaracteristicas(AgentesCaracteristicasData datos,params SqlTransaction[] transaction)
		{
			bool response = false;
			try
			{
				foreach (DataRow row in datos.Rows)
				{
					SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, row[AgentesCaracteristicasData.AGE_ID_FIELD]);
					SqlParameter param2 = new SqlParameter(AGE_DESARROLLO_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_DESARROLLO_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param3 = new SqlParameter(AGE_MANERA_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MANERA_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param4 = new SqlParameter(ARD_ID_PARAM, row[AgentesCaracteristicasData.ARD_ID_FIELD]);
					SqlParameter param5 = new SqlParameter(AGE_OTRA_AREA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_FIELD]);
					SqlParameter param6 = new SqlParameter(AGE_OTRA_AREA_ARTISTICA_TEMA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_ARTISTICA_TEMA_FIELD]);
					SqlParameter param7 = new SqlParameter(AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_FIELD]);
					SqlParameter param8 = new SqlParameter(AGE_ORGANIZACION_ARTISTICA_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ORGANIZACION_ARTISTICA_ACTUAL_FIELD]);
					SqlParameter param9 = new SqlParameter(AGE_ANO_VINCULACION_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ANO_VINCULACION_ACTUAL_FIELD]);
					SqlParameter param10 = new SqlParameter(AGE_MODALIDAD_FORMACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MODALIDAD_FORMACION_ARTISTICA_FIELD]);
					SqlParameter param11 = new SqlParameter(NIV_ID_PARAM, row[AgentesCaracteristicasData.NIV_ID_FIELD]);
					SqlParameter param12 = new SqlParameter(AGE_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_TITULO_FIELD]);
					SqlParameter param13 = new SqlParameter(AGE_ANO_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_ANO_TITULO_FIELD]);
					SqlParameter param14 = new SqlParameter(AGE_ENTIDAD_EDUCATIVA_PARAM, row[AgentesCaracteristicasData.AGE_ENTIDAD_EDUCATIVA_FIELD]);
					SqlParameter param15 = new SqlParameter(AGE_EDU_EN_CURSO_PARAM,System.DBNull.Value);
					if (row[AgentesCaracteristicasData.AGE_EDU_EN_CURSO_FIELD]!=null)
						param15.Value = row[AgentesCaracteristicasData.AGE_EDU_EN_CURSO_FIELD];

					SqlParameter param16 = new SqlParameter(AGE_EDU_SEMESTRES_PARAM,System.DBNull.Value);
					if (row[AgentesCaracteristicasData.AGE_EDU_SEMESTRES_FIELD]!=null)
						param16.Value = row[AgentesCaracteristicasData.AGE_EDU_SEMESTRES_FIELD];

					SqlParameter param17 = new SqlParameter(AGE_EDU_ENTIDAD_PARAM, System.DBNull.Value);
					if (row[AgentesCaracteristicasData.AGE_EDU_ENTIDAD_FIELD]!=null)
						param17.Value = row[AgentesCaracteristicasData.AGE_EDU_ENTIDAD_FIELD];


					SqlParameter[] parameters = {param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14,param15,param16,param17};
					if (transaction.Length>0)
						SqlHelper.ExecuteNonQuery(transaction[0], CommandType.StoredProcedure, "PAS_ADICIONAR_ART_AGENTES_CARACTERISTICAS", parameters);
					else
                        SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PAS_ADICIONAR_ART_AGENTES_CARACTERISTICAS", parameters);

				}
				response = true;
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ADICIONAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorInsertar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			return response;
		}

		public void ActualizarAgentesCaracteristicas(AgentesCaracteristicasData datos)
		{
			try
			{
				foreach (DataRow row in datos.Rows)
				{
					SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, row[AgentesCaracteristicasData.AGE_ID_FIELD]);
					SqlParameter param2 = new SqlParameter(AGE_DESARROLLO_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_DESARROLLO_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param3 = new SqlParameter(AGE_MANERA_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MANERA_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param4 = new SqlParameter(ARD_ID_PARAM, row[AgentesCaracteristicasData.ARD_ID_FIELD]);
					SqlParameter param5 = new SqlParameter(AGE_OTRA_AREA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_FIELD]);
					SqlParameter param6 = new SqlParameter(AGE_OTRA_AREA_ARTISTICA_TEMA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_ARTISTICA_TEMA_FIELD]);
					SqlParameter param7 = new SqlParameter(AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_FIELD]);
					SqlParameter param8 = new SqlParameter(AGE_ORGANIZACION_ARTISTICA_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ORGANIZACION_ARTISTICA_ACTUAL_FIELD]);
					SqlParameter param9 = new SqlParameter(AGE_ANO_VINCULACION_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ANO_VINCULACION_ACTUAL_FIELD]);
					SqlParameter param10 = new SqlParameter(AGE_MODALIDAD_FORMACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MODALIDAD_FORMACION_ARTISTICA_FIELD]);
					SqlParameter param11 = new SqlParameter(NIV_ID_PARAM, row[AgentesCaracteristicasData.NIV_ID_FIELD]);
					SqlParameter param12 = new SqlParameter(AGE_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_TITULO_FIELD]);
					SqlParameter param13 = new SqlParameter(AGE_ANO_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_ANO_TITULO_FIELD]);
					SqlParameter param14 = new SqlParameter(AGE_ENTIDAD_EDUCATIVA_PARAM, row[AgentesCaracteristicasData.AGE_ENTIDAD_EDUCATIVA_FIELD]);
					SqlParameter param15 = new SqlParameter(AGE_EDU_EN_CURSO_PARAM, row[AgentesCaracteristicasData.AGE_EDU_EN_CURSO_FIELD]);
					SqlParameter param16 = new SqlParameter(AGE_EDU_SEMESTRES_PARAM, row[AgentesCaracteristicasData.AGE_EDU_SEMESTRES_FIELD]);
					SqlParameter param17 = new SqlParameter(AGE_EDU_ENTIDAD_PARAM, row[AgentesCaracteristicasData.AGE_EDU_ENTIDAD_FIELD]);

					SqlParameter[] parameters = {param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14,param15,param16,param17};	
                    SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PAS_ACTUALIZAR_ART_AGENTES_CARACTERISTICAS", parameters);
				}
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ACTUALIZAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorActualizar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
		}
		public bool Actualiza(AgentesCaracteristicasData datos,params SqlTransaction[] transaction)
		{
			int cant=0;
			bool response;
			try
			{
				foreach (DataRow row in datos.Rows)
				{
					SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, row[AgentesCaracteristicasData.AGE_ID_FIELD]);
					SqlParameter param2 = new SqlParameter(AGE_DESARROLLO_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_DESARROLLO_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param3 = new SqlParameter(AGE_MANERA_PRACTICA_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MANERA_PRACTICA_ARTISTICA_FIELD]);
					SqlParameter param4 = new SqlParameter(ARD_ID_PARAM, row[AgentesCaracteristicasData.ARD_ID_FIELD]);
					SqlParameter param5 = new SqlParameter(AGE_OTRA_AREA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_FIELD]);
					SqlParameter param6 = new SqlParameter(AGE_OTRA_AREA_ARTISTICA_TEMA_PARAM, row[AgentesCaracteristicasData.AGE_OTRA_AREA_ARTISTICA_TEMA_FIELD]);
					SqlParameter param7 = new SqlParameter(AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_HACE_PARTE_ORGANIZACION_ARTISTICA_FIELD]);
					SqlParameter param8 = new SqlParameter(AGE_ORGANIZACION_ARTISTICA_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ORGANIZACION_ARTISTICA_ACTUAL_FIELD]);
					SqlParameter param9 = new SqlParameter(AGE_ANO_VINCULACION_ACTUAL_PARAM, row[AgentesCaracteristicasData.AGE_ANO_VINCULACION_ACTUAL_FIELD]);
					SqlParameter param10 = new SqlParameter(AGE_MODALIDAD_FORMACION_ARTISTICA_PARAM, row[AgentesCaracteristicasData.AGE_MODALIDAD_FORMACION_ARTISTICA_FIELD]);
					SqlParameter param11 = new SqlParameter(NIV_ID_PARAM, row[AgentesCaracteristicasData.NIV_ID_FIELD]);
					SqlParameter param12 = new SqlParameter(AGE_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_TITULO_FIELD]);
					SqlParameter param13 = new SqlParameter(AGE_ANO_TITULO_PARAM, row[AgentesCaracteristicasData.AGE_ANO_TITULO_FIELD]);
					SqlParameter param14 = new SqlParameter(AGE_ENTIDAD_EDUCATIVA_PARAM, row[AgentesCaracteristicasData.AGE_ENTIDAD_EDUCATIVA_FIELD]);
					SqlParameter param15 = new SqlParameter(AGE_EDU_EN_CURSO_PARAM, row[AgentesCaracteristicasData.AGE_EDU_EN_CURSO_FIELD]);
					SqlParameter param16 = new SqlParameter(AGE_EDU_SEMESTRES_PARAM, row[AgentesCaracteristicasData.AGE_EDU_SEMESTRES_FIELD]);
					SqlParameter param17 = new SqlParameter(AGE_EDU_ENTIDAD_PARAM, row[AgentesCaracteristicasData.AGE_EDU_ENTIDAD_FIELD]);

					SqlParameter[] parameters = {param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14,param15,param16,param17};
					if (transaction.Length>0)
						cant += SqlHelper.ExecuteNonQuery(transaction[0], CommandType.StoredProcedure, "PAS_ACTUALIZAR_ART_AGENTES_CARACTERISTICAS", parameters);
					else
						cant += SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PAS_ACTUALIZAR_ART_AGENTES_CARACTERISTICAS", parameters);
				}
				if (cant>0)
					response = true;
				else
					response = false;
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ACTUALIZAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorActualizar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			return response;
		}

		/// <summary>
		/// Elimina un registro de la entidad Artagentescaracteristicas
		/// </summary>
		/// <param name="parAGE_ID">Identificador del registro
		public void Eliminar(decimal parAGE_ID,params SqlTransaction[] transaction)
		{			
			SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, parAGE_ID);
			SqlParameter[] parameters = {param1};
			try
			{				
				if (transaction.Length>0)
					SqlHelper.ExecuteNonQuery(transaction[0], CommandType.StoredProcedure, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS", parameters);
				else
                    SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS", parameters);				
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorEliminar", this.GetType().FullName) + ".<br>" + ex.Message, ex);
			}			
		}

		/// <summary>
		/// Elimina un registro de la entidad Artagentescaracteristicas
		/// </summary>
		/// <param name="parAGE_ID">Identificador del registro
		public void EliminarAgentesCaracteristicas(decimal parAGE_ID)
		{
			SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, parAGE_ID);
			SqlParameter[] parameters = {param1};
			try
			{				
				SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS", parameters);
			}
			catch (SqlException sqlEx)
			{
				throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorEliminar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
		}

		public void EliminarAgentesCaracteristicas(ArrayList arrIDs)
		{
			SqlConnection objConnection = new SqlConnection(ConnectionString);
			objConnection.Open();
			SqlTransaction objTrans = objConnection.BeginTransaction();

			foreach (object Item in arrIDs)
			{
				SqlParameter param1 = new SqlParameter(AGE_ID_PARAM, decimal.Parse(Item.ToString()));
				SqlParameter[] parameters = {param1};
				try
				{					
					SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS", parameters);			
				}
				catch (SqlException sqlEx)
				{
					objTrans.Rollback();
					objConnection.Close();
					throw new ApplicationExceptionManager(Utilidad.DisplaySqlErrors(sqlEx, "PAS_ELIMINAR_ART_AGENTES_CARACTERISTICAS"), sqlEx);
				}
				catch (Exception ex)
				{
					objTrans.Rollback();
					objConnection.Close();
					throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorEliminar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
				}				
			}
			objTrans.Commit();
			objConnection.Close();
		}

		public bool Actualizar(AgentesCaracteristicasData dtGenerales, AgentesAreasTemasData dtTemas, params SqlTransaction[] itransac)
		{
			bool response = false;
			try
			{
				if (Actualiza(dtGenerales, itransac))
				{
					response = true;
					AgentesAreasTemas objTemas = new AgentesAreasTemas(ConnectionString);						
					objTemas.EliminarAgentesAreasTemas(Convert.ToDecimal(dtGenerales.Rows[0][AgentesCaracteristicasData.AGE_ID_FIELD].ToString()),itransac);					
					if (dtTemas.Rows.Count>0)
						objTemas.Adicionar(dtTemas,itransac);
				}
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorActualizar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			return response;
		}
		public bool Adicionar(AgentesCaracteristicasData dtGenerales, AgentesAreasTemasData dtTemas, params SqlTransaction[] itransac)
		{
			try
			{
				AdicionarAgentesCaracteristicas(dtGenerales, itransac);			
				if (dtTemas.Rows.Count>0)			
				{
					AgentesAreasTemas objTemas = new AgentesAreasTemas(ConnectionString);
					objTemas.Adicionar(dtTemas,itransac);						
				}
			}
			catch (Exception ex)
			{
				throw new ApplicationExceptionManager(Utilidad.TraerMensaje("gmsgErrorInsertar", this.GetType().FullName)+ ".<br>" + ex.Message, ex);
			}
			return true;
		}
	}
}